Three steps to SQL tuning

There are three main steps to the SQL tuning process:

1. Isolate the problem to a single SQL

   You may be lucky and know already the exact SQL causing the problem. If so, move straight on to the second step. Otherwise, click on the link above for help on finding the problem SQL.

2. Analyse the SQL to determine the nature of the problem

   Most performance problems are quite common and easy to fix. This section will describe some of these and how to spot them, and then go on to describe a more general analysis method.

3. Fix it.

   Almost every performance problem has a solution; it's just that some are more complex than others. In order of increasing complexity and expense, such fixes include:

   • Analyze the underlying table to give Oracle's Cost Based Optimizer the information it needs to resolve the SQL efficiently.
   • Add one or more hints to the SQL to encourage or discourage certain execution plans.
   • Minor changes to the SQL to encourage or discourage certain execution plans.
   • Restructure a poorly designed SQL that cannot be resolved efficiently.
   • Alter the underlying infrastructure. eg. Add or change (or even remove!) indexes; introduce clusters, partitions or index-organised tables; denormalize tables; use materialized views. Note that these actions venture outside the scope of this document, and should only be performed with the prior permission of (and preferably assistance from) the DBA and/or System Architect.
   • Refer the problem to the database administrator. Possible solutions here would include tuning the Oracle instance, restructuring or moving tablespaces, or archiving old data.
   • Refer the problem to the System Adminstrator. Possible solutions may include reconfiguration of existing hardware, or acquisition of new hardware.

   No links are provided here for fixes. Every problem is different, and the fixes are a logical progression of the analysis. Follow the link for Step 2 above to determine the problem and see the recommended fix. If this does not work, consult a more experienced SQL Tuner, the System Architect, or the DBA.